with tab_waybill as ( --从订单表中取运单信息信息（运单号、取件网点、派件网点）
    select
        waybill_no
         ,pick_network_code
         ,terminal_dispatch_code
         ,dispatch_network_code
         ,input_time
         ,package_inside_charge_weight
    from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
    where substr(dt,1,10)<=date_add('{{ execution_date |  cst_ds }}',0)
      and substr(dt,1,10)>=date_add('{{ execution_date |  cst_ds }}',-14)
      and dispatch_network_code is not NULL
),
     tab_taking as (--取运单揽收时间
         select
             waybill_no,scan_time,scan_date,scan_site_code
         from
             (
                 select
                     waybill_no,
                     scan_time,
                     date(scan_time) scan_date,
             scan_site_code,
             ROW_NUMBER() over(partition by waybill_no,scan_site_code order by scan_time) rk
from jms_dwd.dwd_tab_barscan_taking_base_dt
where dt>=date_add('{{ execution_date |  cst_ds }}',-14) and dt<='{{ execution_date |  cst_ds }}'
    )t where rk=1 and date(scan_time) between date_add('{{ execution_date |  cst_ds }}',-14) and '{{ execution_date |  cst_ds }}'
    )

--将运单信息补充完整（运单号、取件网点、派件网点）+（内部计费重量,揽收时间）
insert overwrite table jms_dm.dm_waybill_simple_info_dt partition(dt)
select
    tab_waybill.waybill_no,
    tab_waybill.pick_network_code,
    tab_waybill.terminal_dispatch_code,
    tab_waybill.dispatch_network_code,
    tab_waybill.package_inside_charge_weight,
    tab_taking.scan_time as taking_scan_time,
    nvl(tab_taking.scan_date,date(tab_waybill.input_time)) as taking_scan_date,
    date_format(tab_taking.scan_time,'HH:mm')  as taking_scan_hs,
    nvl(tab_taking.scan_date,date(tab_waybill.input_time)) as dt
from tab_waybill
    left join tab_taking on tab_taking.waybill_no=tab_waybill.waybill_no and tab_taking.scan_site_code=tab_waybill.pick_network_code
distribute by dt,pmod(hash(waybill_no),50)
;


